package com.lovo.sgproj.dao;

import com.lovo.sgproj.bean.StudentBean;

import java.util.ArrayList;

public interface StudentDAO {
    //select * from t_student where fk_classid = ?
    public ArrayList<StudentBean> findStudentByClassID(int classID);


    //SELECT * FROM t_student WHERE fk_roomid=?;
    public ArrayList<StudentBean> findStudentByRoomID(int roomID);


    //select * from t_student stu join t_class cls ON stu.fk_classid = cls.pk_classid
    //		JOIN t_room room ON stu.fk_roomid = room.pk_roomid
    public ArrayList<StudentBean> findAllStudent();


    //1、insert into t_student(.....)values(....)
    //2、update t_room set f_inNum = f_inNum + 1 where pk_roomid = ?
    public void insertStudent(StudentBean stu);


    //1、update t_room set f_inNum = f_inNum - 1 where pk_roomid = (select fk_roomid from t_student where pk_studentid = ?)
    //2、delete from t_student where pk_studentid = ?
    public void deleteStudent(int studentID);

    //select * from t_student stu join t_class cls ON stu.fk_classid = cls.pk_classid
    //		JOIN t_room room ON stu.fk_roomid = room.pk_roomid where stu.pk_studentid = ?
    public StudentBean getStudentByID(int studentID);


    //1、update t_room set f_inNum = f_inNum - 1 where pk_roomid = (select fk_roomid from t_student where pk_studentid = ?)
    //2、update t_room set f_inNum = f_inNum + 1 where pk_roomid = ?
    //3、update t_student set fk_roomid = ? where pk_studentid = ?
    public void updateStudentRoom(int studentID, int roomID);


    //select * from t_student stu join t_class cls ON stu.fk_classid = cls.pk_classid
    //		JOIN t_room room ON stu.fk_roomid = room.pk_roomid
    //where stu.f_studentname like ? and cls.f_classname like ? and room.f_roomaddress like ?
    public ArrayList<StudentBean> findStudentByCondition(String stuName, String className, String roomAddress);

}
